package com.sx.core.utils;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

//import sun.security.util.Password;


/**
 * Created by IntelliJ IDEA. User: kangpan Date: 22/3/12 Time: 11:42 AM To change this template use File | Settings |
 * File Templates.
 *
 * @author $author$
 * @version $Revision$, $Date$
 */
public class ExcelUtils {
    static final public String xls=".xls";
    static final public String xlsx=".xlsx";
    static final public Log log= LogFactory.getLog(ExcelUtils.class);
    public interface DoImportCallback{
        void doImport(List<Map<String, Object>> list);
    }
    public static void main(String[] args) throws Exception {
        Columns columns1=new Columns();
        Column r1_c0=new Column(0,"学号",0,0,"xh");
        Column r1_c1=new Column(1,"姓名",0,0,"xm");
        Column r1_c2=new Column(2,"项目",0,0,"project");
        Column r1_c3=new Column(3,"金额",0,0,"je");
        Column r1_c4=new Column(4,"年级",0,0,"grade");
        Column r1_c5=new Column(5,"班级",0,0,"clazz");
//        Column r1_c6=new Column(6,"",0,0,"");
//        Column r1_c7=new Column(7,"个人简历",0,5,"");
//        Column r1_c8=new Column(8,"",0,0,"");
//        Column r1_c9=new Column(9,"",0,0,"");
//        Column r1_c10=new Column(10,"",0,0,"");
//        Column r1_c11=new Column(11,"",0,0,"");
//        Column r1_c12=new Column(12,"",0,0,"");
        columns1.addColumn(r1_c0)
                .addColumn(r1_c1)
                .addColumn(r1_c2)
                .addColumn(r1_c3)
                .addColumn(r1_c4)
                .addColumn(r1_c5)
//                .addColumn(r1_c6)
//                .addColumn(r1_c7)
//                .addColumn(r1_c8)
//                .addColumn(r1_c9)
//                .addColumn(r1_c10)
//                .addColumn(r1_c11)
//                .addColumn(r1_c12)
        ;
        Columns columns2=new Columns();
        Column r2_c0=new Column(0,"",0,0,"");
        Column r2_c1=new Column(1,"",0,0,"");
        Column r2_c2=new Column(2,"",0,0,"");
        Column r2_c3=new Column(3,"起时间",0,0,"sxStartDate");
        Column r2_c4=new Column(4,"止时间",0,0,"sxEndDate");
        Column r2_c5=new Column(5,"起时间",0,0,"sxStartDate");
        Column r2_c6=new Column(6,"止时间",0,0,"sxEndDate");

        Column r2_c7=new Column(7,"简历1（起止时间）",0,0,"jlStartDate1");
        Column r2_c8=new Column(8,"简历1（内容）",0,0,"jlContent1");
        Column r2_c9=new Column(9,"简历2（起止时间）",0,0,"jlStartDate2");
        Column r2_c10=new Column(10,"简历2（内容）",0,0,"jlContent2");
        Column r2_c11=new Column(11,"简历3（起止时间）",0,0,"jlStartDate3");
        Column r2_c12=new Column(12,"简历3（内容）",0,0,"jlContent3");
        columns2.addColumn(r2_c0)
                .addColumn(r2_c1)
                .addColumn(r2_c2)
                .addColumn(r2_c3)
                .addColumn(r2_c4)
                .addColumn(r2_c5)
                .addColumn(r2_c6)
                .addColumn(r2_c7)
                .addColumn(r2_c8)
                .addColumn(r2_c9)
                .addColumn(r2_c10)
                .addColumn(r2_c11)
                .addColumn(r2_c12);
        Row row1=new Row(1,columns1);
        Row row2=new Row(2,columns2);
        Rows rows=new Rows();
        rows.addRow(row1);
//        rows=createRosterRows();
        List<Map<String,Object>> data=new ArrayList<Map<String, Object>>();
        for(int i=0;i<3000;i++){
            Map<String,Object> map1=new HashMap<String, Object>();
            map1.put("xm","测试"+i);
            map1.put("xh",UUIDUtils.generateUuid8());
            map1.put("project","收费"+i%100);
            map1.put("je",new BigDecimal(new Random().nextDouble()*100).divide(new BigDecimal(100), 2, RoundingMode.HALF_UP).add(new BigDecimal(i)).toString());
            map1.put("grade",(i+1)%10+"年级");
            map1.put("clazz",(i+1)%30+"班级");
            data.add(map1);
        }



        Map<String,Object> map2=new HashMap<String, Object>();
        map2.put("name","134");
        map2.put("htStartDate","2014-01-23");
        map2.put("htEndDate","2014-01-24");
        map2.put("sxStartDate","2014-01-25");
        map2.put("sxEndDate","2014-01-26");
        map2.put("jlStartDate1","2014-01-27");
        map2.put("jlContent1","1111");
        map2.put("jlStartDate2","2014-01-27");
        map2.put("jlContent2","222");
        map2.put("jlStartDate3","2014-01-27");
        map2.put("jlContent3","333");
        data.add(map2);


//        Workbook wb1 = export("批量收费",xlsx, rows, data);
//        FileOutputStream os1 = new FileOutputStream("/Users/lihao/Downloads/批量收费.xlsx");
//        wb1.write(os1);
//        os1.close();
//        InputStream inputStream=new FileInputStream("/Users/lihao/Downloads/workbook.xls");
//        readExcel(rows,inputStream,0);

        Map<Integer,String> columns=new HashMap<>();
        columns.put(0,"sqrn");
        columns.put(1,"gkh");
        columns.put(2,"sqh");
        columns.put(3,"bt");
        columns.put(4,"sqren");
        columns.put(5,"bzsqr");
        columns.put(6,"fmr");
        columns.put(7,"dyfmr");
        columns.put(8,"yxqr");
        columns.put(9,"gkr");
        columns.put(10,"gjzfl");
        columns.put(11,"gjfl");
        columns.put(12,"zld");
        columns.put(13,"tzd");
        columns.put(14,"zllx");
        columns.put(15,"yys");
        columns.put(16,"zyys");
        columns.put(17,"fzyys");
        columns.put(18,"yygss");
        columns.put(19,"byys");
        columns.put(20,"bzyy");
        columns.put(21,"fbzyys");
        columns.put(22,"byygss");
        columns.put(23,"byygjs");
        columns.put(24,"tzs");
        columns.put(25,"tzgjs");
        columns.put(26,"dj");
        columns.put(27,"xgd");
        columns.put(28,"flzt");
        columns.put(29,"zlms");
        ExcelUtils.doImport(FileUtils.openInputStream(new File("/Users/lihao/Downloads/第三季度生活费.xlsx")),xlsx, 0,3, 30000,columns, new ExcelUtils.DoImportCallback(){
            @Override
            public void doImport(List<Map<String, Object>> list) {
                int len=list.size();
                int count=0;
                double allMoney=0;

            }
        });

    }
    public static Workbook exportResultSet(String sheetName,String ext, Rows rows, ResultSet resultSet ) {
        Workbook wb=null;
        if(ext.equals(xls)){
            wb = new HSSFWorkbook();
        }else{
            wb = new XSSFWorkbook();
        }
        Sheet sheet = wb.createSheet(sheetName);
        Map<String,CellStyle> styleMap=createStyles(wb);
        Cell cell =null;
        List<CellRangeAddress> cellRangeAddresses=new ArrayList<CellRangeAddress>();
        List<Row> rows_list=rows.getRows();
        for(int i=0;i<rows_list.size();i++){
            Row row=rows_list.get(i);
            int r_index=row.getIndex();
            org.apache.poi.ss.usermodel.Row sheetRow = sheet.createRow(r_index);
            sheetRow.setHeight((short)(450+450*i));
            List<Column> columns=row.getColumns().getColumns();
            for(Column column : columns){
                int c_index=column.getIndex();
                String header=column.getHeader();
                String style=column.getStyle();
                cell = sheetRow.createCell(c_index);
                sheet.setColumnWidth(c_index, column.getWidth()==0?3500:column.getWidth());
                cell.setCellStyle(styleMap.get(style));
                cell.setCellValue(header);
                int colSpan=column.getColSpan();
                if(colSpan!=0){
                    CellRangeAddress cellRangeAddress=new CellRangeAddress(r_index,(short)(r_index+colSpan),c_index,(short)c_index);
                    cellRangeAddresses.add(cellRangeAddress);
                }
                int cellSpan=column.getCellSpan();
                if(cellSpan!=0){
                    CellRangeAddress cellRangeAddress=new CellRangeAddress(r_index,(short)(r_index),c_index,(short)(c_index+cellSpan));
                    cellRangeAddresses.add(cellRangeAddress);
                }
            }
        }
        for(CellRangeAddress cellRangeAddress : cellRangeAddresses){
            sheet.addMergedRegion(cellRangeAddress);
        }

        Row dataRow=rows.toDataRow();
        List<Column> dataColumns=dataRow.getColumns().getColumns();

        // 设置单元格内容格式
        CellStyle style1 = wb.createCellStyle();
        style1.setWrapText(true);// 自动换行

        try{
            int i=0;
            while (resultSet.next()){
                org.apache.poi.ss.usermodel.Row sheetRow = sheet.createRow(dataRow.getIndex()+i);
                sheetRow.setHeight((short)450);
                for(int j=0;j<dataColumns.size();j++){
                    Column column=dataColumns.get(j);
                    String dataIndex=column.getDataIndex();
                    String style=column.getStyle();
                    int c_index=column.getIndex();
                    cell=sheetRow.createCell(c_index);
                    cell.setCellStyle(styleMap.get(style));
                    cell.setCellValue(ObjectUtils.toString(resultSet.getString(dataIndex)));
                }
                i++;
            }
        }catch (Exception e){
            log.error("export",e);
        }
        return wb;
    }
    public static Workbook export(String sheetName,String ext, Rows rows, List<? extends Map<String,Object>> contexts ) {
        Workbook wb=null;
        if(ext.equals(xls)){
            wb = new HSSFWorkbook();
        }else{
            wb = new XSSFWorkbook();
        }
        Sheet sheet = wb.createSheet(sheetName);
        Map<String,CellStyle> styleMap=createStyles(wb);
        Cell cell =null;
        List<CellRangeAddress> cellRangeAddresses=new ArrayList<CellRangeAddress>();
        List<Row> rows_list=rows.getRows();
        for(int i=0;i<rows_list.size();i++){
            Row row=rows_list.get(i);
            int r_index=row.getIndex();
            org.apache.poi.ss.usermodel.Row sheetRow = sheet.createRow(r_index);
            sheetRow.setHeight((short)(450+450*i));
            List<Column> columns=row.getColumns().getColumns();
            for(Column column : columns){
                int c_index=column.getIndex();
                String header=column.getHeader();
                String style=column.getStyle();
                cell = sheetRow.createCell(c_index);
                sheet.setColumnWidth(c_index, column.getWidth()==0?3500:column.getWidth());
                cell.setCellStyle(styleMap.get(style));
                cell.setCellValue(header);
                int colSpan=column.getColSpan();
                if(colSpan!=0){
                    CellRangeAddress cellRangeAddress=new CellRangeAddress(r_index,(short)(r_index+colSpan),c_index,(short)c_index);
                    cellRangeAddresses.add(cellRangeAddress);
                }
                int cellSpan=column.getCellSpan();
                if(cellSpan!=0){
                    CellRangeAddress cellRangeAddress=new CellRangeAddress(r_index,(short)(r_index),c_index,(short)(c_index+cellSpan));
                    cellRangeAddresses.add(cellRangeAddress);
                }
            }
        }
        for(CellRangeAddress cellRangeAddress : cellRangeAddresses){
            sheet.addMergedRegion(cellRangeAddress);
        }

        Row dataRow=rows.toDataRow();
        List<Column> dataColumns=dataRow.getColumns().getColumns();

        // 设置单元格内容格式
        CellStyle style1 = wb.createCellStyle();
        style1.setWrapText(true);// 自动换行
        for(int i=0;i<contexts.size();i++){
            Map<String,Object> data=contexts.get(i);
            org.apache.poi.ss.usermodel.Row sheetRow = sheet.createRow(dataRow.getIndex()+i);
            sheetRow.setHeight((short)450);
            for(int j=0;j<dataColumns.size();j++){
                Column column=dataColumns.get(j);
                String dataIndex=column.getDataIndex();
                int c_index=column.getIndex();
                cell=sheetRow.createCell(c_index);
                cell.setCellStyle(styleMap.get(StringUtils.defaultIfBlank(ObjectUtils.toString(data.get("style")),"cell")));
                ColumnRenderListener renderListener=column.getRenderListener();
                if(renderListener!=null){
                    String dd=renderListener.renderer(ObjectUtils.toString(data.get(dataIndex)),data);
                    cell.setCellValue(ObjectUtils.toString(dd));
                }else{
                    cell.setCellValue(ObjectUtils.toString(data.get(dataIndex)));
                }
            }
        }
        return wb;
    }
    public static void doImport(InputStream is,String ext,int sheetIndex,int firstRow,int flushRow,Map<Integer,String> columns,DoImportCallback callback){
        POIFSFileSystem fs = null;
        Workbook wb = null;
        List rowData=new ArrayList<>();
        try {
            if(ext.equals(xls)){
                fs = new POIFSFileSystem(is);
                wb = new HSSFWorkbook(fs);
            }else{
                wb = new XSSFWorkbook(is);
            }

            Sheet sheet=wb.getSheetAt(sheetIndex);
            int rowSum=sheet.getPhysicalNumberOfRows();
            rowSum=Math.min(30000,rowSum);
            if(rowSum<1){
                return;
            }
            boolean submit=rowSum>flushRow;
//            int has_firstRow=sheet.getFirstRowNum();
            firstRow=firstRow-1;
//            if(firstRow>has_firstRow){
//                return;
//            }

            for(int j=firstRow;j<rowSum;j++){
                int cellCount = sheet.getRow(j).getLastCellNum();
                if(j!=0 && j%flushRow==0 && submit){
                    callback.doImport(rowData);
                    rowData.clear();
                }
                Map data = new HashMap();
                for(int k=0;k<cellCount;k++){
                    Cell cell = sheet.getRow(j).getCell(k);
                    String value = "";
                    if(cell==null){
                        continue;
                    }
                    switch (cell.getCellType()){
                        case Cell.CELL_TYPE_NUMERIC:
                            HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                            String cellFormatted = dataFormatter.formatCellValue(cell);
                            value=cellFormatted;
                            break;
                        default:{
                            value = ObjectUtils.toString(cell).trim();
                        }
                    }
                    if(columns.containsKey(k)){
                        data.put(columns.get(k),value);
                    }
                }
                rowData.add(data);
            }
        }catch (Exception e){
            log.error("import ex",e);
        }finally {
            close(fs,wb,is);
        }
        callback.doImport(rowData);
    }

    private static void close(POIFSFileSystem fs,Workbook wb,InputStream is){
        if(fs!=null){
            try {
                fs.close();
            } catch (IOException e) {
            }
        }
        if(wb!=null){
            try {
                wb.close();
            } catch (IOException e) {
            }
        }
        if(is!=null){
            try {
                is.close();
            } catch (IOException e) {
            }
        }
    }

    // end method readExcel
    public static List readGridExcel(InputStream is,String ext,int maxRow) {
        POIFSFileSystem fs=null;
        Workbook wb=null;
        try {
            if (ext.equals(xls)) {
                fs = new POIFSFileSystem(is);
                wb = new HSSFWorkbook(fs);
            } else {
                wb = new XSSFWorkbook(is);
            }

            int sheets = wb.getNumberOfSheets();
            List sheetList = new ArrayList<>();
            for (int i = 0; i < sheets; i++) {
                Sheet sheet = wb.getSheetAt(i);
                String sheetName = sheet.getSheetName();
                Map map = new HashMap<>();
                map.put("sheetName", sheetName);
                int rowSum = sheet.getPhysicalNumberOfRows() + 1;
                rowSum = Math.min(maxRow, rowSum);
                if (rowSum < 1) {
                    continue;
                }
                int firstRow = sheet.getFirstRowNum();
                int firstCellCount = sheet.getRow(firstRow).getLastCellNum();

                List rowData = new ArrayList<>();
                for (int j = firstRow; j < rowSum; j++) {
                    int cellCount = sheet.getRow(j).getLastCellNum();
                    Map data = new HashMap();
                    data.put("id", j);
                    for (int k = 0; k < cellCount; k++) {
                        org.apache.poi.ss.usermodel.Row row=sheet.getRow(j);
                        if(row==null){
                            continue;
                        }
                        Cell cell = row.getCell(k);
                        if (cell == null) {
                            continue;
                        }
                        String value = "";
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC:
                                HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                                String cellFormatted = dataFormatter.formatCellValue(cell);
                                value=cellFormatted;
//                                value=new BigDecimal(cell.getNumericCellValue()*10000000000000000l).divide(new BigDecimal(10000000000000000l)).toString();
                                break;
                            default: {
                                value = ObjectUtils.toString(cell).trim();
                            }
                        }
                        data.put("filed" + k, value);

                    }
                    if(data.size()>1){
                        rowData.add(data);
                    }

                }
                map.put("data", rowData);
                map.put("cellCount", firstCellCount);
                sheetList.add(map);
            }
            return sheetList;
        }catch (Exception e){
            log.error("import error",e);
            return null;
        }finally {
            close(fs,wb,is);
        }

    }

    private static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleFont.setColor(IndexedColors.ROYAL_BLUE.getIndex());
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);

        Font monthFont = wb.createFont();
        monthFont.setFontHeightInPoints((short) 14);
//        monthFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
//        monthFont.setColor(IndexedColors.WHITE.getIndex());
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(monthFont);
        style.setWrapText(true);
        styles.put("header", style);

        Font cellFont = wb.createFont();
//        cellFont.setFontHeightInPoints((short) 12);
//        cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setWrapText(true);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cellFont);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
        styles.put("cell", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cellFont);
//    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
        styles.put("formula", style);


        cellFont = wb.createFont();
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setWrapText(true);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cellFont);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        styles.put("fail", style);

        Font cellFont1 = wb.createFont();
        cellFont1.setFontHeightInPoints((short) 12);
        cellFont1.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setLocked(false);
        style.setWrapText(true);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cellFont1);
        styles.put("cellUnlocked", style);

        return styles;
    }
    public static interface ColumnRenderListener{
        String renderer(String data, Map<String, Object> record);
    }



    public static class Column implements Comparable,Serializable {
        private int index;
        private String header;
        private int colSpan;
        private int cellSpan;
        private String dataIndex;
        private int width;
        private String style="header";
        private ColumnRenderListener renderListener;

        public Column(int index, String header, int colSpan, int cellSpan, String dataIndex, int width) {
            this.index = index;
            this.header = header;
            this.colSpan = colSpan;
            this.cellSpan = cellSpan;
            this.dataIndex=dataIndex;
            this.width=width;
        }
        public Column(int index, String header, int colSpan, int cellSpan, String dataIndex) {
            this.index = index;
            this.header = header;
            this.colSpan = colSpan;
            this.cellSpan = cellSpan;
            this.dataIndex=dataIndex;
        }
        public Column(int index, String header, int colSpan, int cellSpan, String dataIndex,String style) {
            this.index = index;
            this.header = header;
            this.colSpan = colSpan;
            this.cellSpan = cellSpan;
            this.dataIndex=dataIndex;
            this.style=style;
        }

        public Column addColumnRenderListener(ColumnRenderListener listener){
            this.renderListener=listener;
            return this;
        }

        public ColumnRenderListener getRenderListener() {
            return renderListener;
        }

        public int getIndex() {
            return index;
        }

        public void setIndex(int index) {
            this.index = index;
        }

        public String getHeader() {
            return header;
        }

        public void setHeader(String header) {
            this.header = header;
        }

        public int getColSpan() {
            return colSpan;
        }

        public void setColSpan(int colSpan) {
            this.colSpan = colSpan;
        }

        public int getCellSpan() {
            return cellSpan;
        }

        public void setCellSpan(int cellSpan) {
            this.cellSpan = cellSpan;
        }

        public String getDataIndex() {
            return dataIndex;
        }

        public void setDataIndex(String dataIndex) {
            this.dataIndex = dataIndex;
        }

        public int getWidth() {
            return width;
        }

        public void setWidth(int width) {
            this.width = width;
        }

        public String getStyle() {
            return style;
        }

        public void setStyle(String style) {
            this.style = style;
        }

        public int compareTo(Object o) {
            Column other=(Column)o;
            if(this.index>other.index){
                return 1;
            }else if(this.index==other.getIndex()){
                return 0;
            }
            return -1;
        }
    }
    public static class Columns implements Serializable{
        private List<Column> columns=new ArrayList<Column>();
        public Columns addColumn(Column column){
            this.columns.add(column);
            return this;
        }
        public Columns addColumns(Column... columns){
            for(Column column:columns)
                this.columns.add(column);
            return this;
        }

        public List<Column> getColumns() {
            Collections.sort(this.columns);
            return columns;
        }

        public void setColumns(List<Column> columns) {
            this.columns = columns;
        }
    }
    public static class Row implements Comparable,Serializable {
        private int index;
        private Columns columns;
        public Row(int index,Columns columns){
            this.index=index;
            this.columns=columns;
        }

        public int getIndex() {
            return index;
        }
        public void setIndex(int index) {
            this.index = index;
        }

        public Columns getColumns() {

            return columns;
        }

        public void setColumns(Columns columns) {
            this.columns = columns;
        }

        public int compareTo(Object o) {
            Row other=(Row)o;
            if(this.index>other.index){
                return 1;
            }else if(this.index==other.getIndex()){
                return 0;
            }
            return -1;
        }
    }
    public static class Rows implements Serializable{
        List<Row> rows=new ArrayList<Row>();
        public Rows addRow(Row row){
            this.rows.add(row);
            return this;
        }

        public List<Row> getRows() {
            Collections.sort(this.rows);
            return rows;
        }
        public Row toDataRow(){
            int index=0;
            Columns list=new Columns();
            List<Integer> indexs=new ArrayList<Integer>();
            for(Row row : this.rows){
                index=row.getIndex();
                indexs.add(index);
                List<Column> columns=row.getColumns().getColumns();
                for(Column c:columns){
                    String dataIndex=c.getDataIndex();
                    if(StringUtils.isNotEmpty(dataIndex)){
                        list.addColumn(c);
                    }
                }
            }
            Collections.sort(indexs);
            if(indexs.size()>0){
                int dataIndex=indexs.get(indexs.size()-1);
                Row row=new Row(dataIndex+1,list);
                return row;
            }
            return null;

        }
    }
} // end class ExcelUtils
